using System;
using ServerNET.Server;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace ServerNET.Server
{
    public class CDatabase
    {
        SqlConnection Connection;

        //public void StringToDate(string DateString, ref int Year, ref int Month, ref int Day)
        //{
        //    DateTime dt = Convert.ToDateTime(DateString);
        //    Year = dt.Year;
        //    Month = dt.Month;
        //    Day = dt.Day;
        //}

        //public int GetOffsetDay(int LastYear, int LastMonth, int LastDay, int NowYear, int NowMonth, int NowDay)
        //{
        //    DateTime t1 = new DateTime(LastYear, LastMonth, LastDay);
        //    DateTime t2 = new DateTime(NowYear, NowMonth, NowDay);

        //    TimeSpan d = t2.Subtract(t1);
        //    return d.Days;

        //    //return (t2.Second - t1.Second) / (3600 * 24);
        //}

        //public void GetLoginResult(int ConnId, string UserName, string Password, ref CUserInfo UserInfo)
        //{
        //    string LastLogin = "";
        //    int DayCount = 1;

        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE UserName='" + UserName + "' AND (Password='" + Password + "' OR SessionId='" + Password + "')", Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            int Active = Convert.ToInt32(reader["Active"]);
        //            //UserInfo.Error = (Active == 0) ? 3 : 0;
        //            UserInfo.UserId = ConnId;
        //            UserInfo.AccountId = Convert.ToInt32(reader["Id"]);
        //            UserInfo.AvatarId = Convert.ToInt32(reader["AvatarId"]);
        //            UserInfo.Exp = Convert.ToInt32(reader["Exp"]);
        //            UserInfo.UserName = reader["UserName"].ToString();
        //            UserInfo.Gold = CCommon.String2Double(reader["VKim"]);
        //            //UserInfo.Version = CGlobal.Config.Version;
        //            DayCount = Convert.ToInt32(reader["DayCount"]);
        //            LastLogin = reader["LastLogin"].ToString();

        //            // First online
        //            if (LastLogin.Length == 0)
        //            {
        //                ExecuteQuery("UPDATE TableUser SET LastLogin=GETDATE(), ReceiveGift=0, DayCount=1 WHERE Id=" + UserInfo.AccountId);
        //            }
        //            else
        //            {
        //                int LastYear = 0, LastMonth = 0, LastDay = 0;
        //                StringToDate(LastLogin, ref LastYear, ref LastMonth, ref LastDay);
        //                DateTime Now = DateTime.Now;
        //                int NowYear = Now.Year;
        //                int NowMonth = Now.Month;
        //                int NowDay = Now.Day;
        //                int OffsetDay = GetOffsetDay(LastYear, LastMonth, LastDay, NowYear, NowMonth, NowDay);
        //                if (OffsetDay <= 0)
        //                {
        //                    ExecuteQuery("UPDATE TableUser SET LastLogin=GETDATE() WHERE Id=" + UserInfo.AccountId);
        //                }
        //                else if (OffsetDay == 1)
        //                {
        //                    DayCount++;
        //                    if (DayCount > 5) DayCount = 1;
        //                    ExecuteQuery("UPDATE TableUser SET LastLogin=GETDATE(), ReceiveGift=0, GiftCount=0, DayCount=" + DayCount + " WHERE Id=" + UserInfo.AccountId);
        //                }
        //                else
        //                {
        //                    ExecuteQuery("UPDATE TableUser SET LastLogin=GETDATE(), ReceiveGift=0, GiftCount=0, DayCount=1 WHERE Id=" + UserInfo.AccountId);
        //                }
        //            }
        //        }
        //        // No Data
        //        else
        //        {
        //            //UserInfo.Error = 2;
        //        }
        //        reader.Close();
        //        Close();
        //    }
        //    catch(Exception ex)
        //    {
        //        Close();
        //        //UserInfo.Error = 5;
        //        CLog.LogError("Error GetLoginResult: " + ex.Message);
        //        CLog.Trace("Error GetLoginResult: " + ex.Source);
        //    }
        //}

        //public int GetUnreadMail(int AccountId)
        //{
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT ToUserId, Status FROM Mail WHERE ToUserId=" + AccountId + " AND Status=0", Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        int Count = 0;
        //        while (reader.Read())
        //        {
        //            Count++;
        //        }
        //        reader.Close();
        //        Close();

        //        return Count;
        //    }
        //    catch (Exception ex)
        //    {
        //        Close();
        //        CLog.LogError("Error GetUnreadMail: " + ex.Message);
        //        CLog.Trace("Error GetUnreadMail: " + ex.Source);
        //    }

        //    return 0;
        //}

        //public int GetBuyAvatarResult(CClient Client, string UserName, int AvatarId, double UserGold, int UserLevel)
        //{
        //    // Precheck Avatar Id
        //    if(AvatarId < 0 || AvatarId > 8)
        //    {
        //        return 1;
        //    }

        //    // Get Info

        //    // Check Gold
        //    double GoldSub = CCommon.AVATAR_COST[AvatarId];
        //    if(UserGold < GoldSub)
        //    {
        //        return 1;
        //    }
        //    double Gold = UserGold - GoldSub;
        //    if(Gold < 0) Gold = 0;

        //    //// Check Level
        //    //int LevelRequire = CCommon.AVATAR_LEVEL[AvatarId];
        //    //if(UserLevel < LevelRequire)
        //    //{
        //    //    return 1;
        //    //}

        //    // Update
        //    ExecuteQuery("UPDATE TableUser SET AvatarId=" + AvatarId + ", VKim=" + Gold + " WHERE Id=" + Client.AccountId);
	        
        //    // Save Log
        //    CLog.LogClientBuyAvatar(UserName, AvatarId, Gold, -GoldSub);

        //    // Update Client
        //    Client.Gold = Gold;
        //    Client.AvatarId = AvatarId;

        //    // Finish
        //    return 0;
        //}

        //public void GetGiftDaily(int AccountId, ref int ReceiveGift, ref int DayCount)
        //{
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE Id=" + AccountId, Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            ReceiveGift = Convert.ToInt32(reader["ReceiveGift"]);
        //            DayCount = Convert.ToInt32(reader["DayCount"]);
        //        }
        //        reader.Close();
        //        Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        Close();
        //        CLog.LogError("Error GetGiftDaily: " + ex.Message);
        //        CLog.Trace("Error GetGiftDaily: " + ex.Source);
        //    }
        //}

        //public void GetGiftTime(int AccountId, ref int ReceiveCount, ref double TimeCountDown)
        //{
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE Id=" + AccountId, Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            ReceiveCount = Convert.ToInt32(reader["GiftCount"]);
        //            TimeCountDown = 0;
        //        }
        //        reader.Close();
        //        Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        Close();
        //        CLog.LogError("Error GetGiftTime: " + ex.Message);
        //        CLog.Trace("Error GetGiftTime: " + ex.Source);
        //    }
        //}

        //public void ReceiveGiftDaily(int AccountId, string UserName, double UserGold, int UserLevel, ref int Result, ref double Gold, ref double GoldAdd)
        //{
        //    // Check
        //    int ReceiveGift = 1, DayCount = 1;
        //    GetGiftDaily(AccountId, ref ReceiveGift, ref DayCount);

        //    // Received
        //    if(ReceiveGift == 1)
        //    {
        //        Result = 1;
        //        return;
        //    }

        //    // Add Gold
        //    GoldAdd = 0;
        //    double[] GoldRand = {3000, 5000, 7000, 10000};
        //    switch(DayCount)
        //    {
        //    case 1:
        //        GoldAdd = 500;
        //        break;
        //    case 2:
        //        GoldAdd = 10000;
        //        break;
        //    case 3:
        //        GoldAdd = 15000;
        //        break;
        //    case 4:
        //        GoldAdd = 20000;
        //        break;
        //    case 5:
        //        GoldAdd = CCommon.RandomNumber(25000, 100000);
        //        break;
        //    }
        //    Gold = UserGold + GoldAdd;

        //    // Update
        //    bool Ok = ExecuteQuery("UPDATE TableUser SET VKim=" + Gold + ", ReceiveGift=1 WHERE Id=" + AccountId);
        //    if(!Ok)
        //    {
        //        Result = 1;
        //        return;
        //    }

        //    // Save Log
        //    CLog.LogClientReceiveDailyGift(UserName, DayCount, Gold, GoldAdd);            
        //}

        //public void ReceiveGiftTime(int AccountId, string UserName, double UserGold, ref int Result, ref int ReceiveCount, ref double TimeCountDown, ref double Gold, ref double GoldAdd)
        //{
        //    // Check
        //    GetGiftTime(AccountId, ref ReceiveCount, ref TimeCountDown);

        //    // Received
        //    if(ReceiveCount >= 3)
        //    {
        //        Result = 1;
        //        return;
        //    }

        //    ReceiveCount ++;

        //    // Add Gold
        //    GoldAdd = 0;
        //    switch(ReceiveCount)
        //    {
        //    case 1:
        //        GoldAdd = 1000;
        //        break;
        //    case 2:
        //        GoldAdd = 2000;
        //        break;
        //    case 3:
        //        GoldAdd = 5000;
        //        break;
        //    }
        //    Gold = UserGold + GoldAdd;

        //    // Update
        //    bool Ok = ExecuteQuery("UPDATE TableUser SET VKim=" + Gold + ", GiftCount=" + ReceiveCount + " WHERE Id=" + AccountId);
        //    if(!Ok)
        //    {
        //        Result = 1;
        //        return;
        //    }

        //    // Save Log
        //    CLog.LogClientReceiveTimeGift(UserName, ReceiveCount, Gold, GoldAdd);            
        //}

        public int GetAddFriendResult(int AccountId, string UserName, int Add, int FriendId)
        {
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE UserName='" + UserName + "'", Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    FriendId = Convert.ToInt32(reader["Id"]);
                }
                else
                {
                    reader.Close();
                    Close();
                    return 1;
                }
                reader.Close();

                // Check Friend In List
                cmd = new SqlCommand("SELECT * FROM FriendList WHERE UserId1=" + AccountId + " AND UserId2=" + FriendId, Connection);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (Add == 1)
                    {
                        reader.Close();
                        return 2;
                    }
                }
                else
                {
                    if (Add != 1)
                    {
                        reader.Close();
                        return 1;
                    }
                }
                reader.Close();
                Close();

                // Add
                if (Add == 1)
                {
                    ExecuteQuery("INSERT INTO FriendList(UserId1, UserId2) VALUES(" + AccountId + "," + FriendId + ")");
                }
                // Remove
                else
                {
                    ExecuteQuery("DELETE FROM FriendList WHERE UserId1=" + AccountId + " AND UserId2=" + FriendId);
                }
            }
            catch (Exception ex)
            {
                Close();
                CLog.LogError("Error GetAddFriendResult: " + ex.Message);
                CLog.Trace("Error GetAddFriendResult: " + ex.Source);
            }

            return 0;
        }

        public int SendMail(int AccountId, string FromName, string ToName, string Title, string Content)
        {
            try
            {
                Open();
                int ReceiveId;
                SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE UserName='" + ToName + "'", Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    ReceiveId = Convert.ToInt32(reader["Id"]);
                }
                else
                {
                    reader.Close();
                    Close();
                    return 1;
                }
                reader.Close();
                Close();

                ExecuteQuery("INSERT INTO Mail(FromUserId, ToUserId, Status, FromUserName, Title, Content, Time) VALUES(" + AccountId +
                    ", " + ReceiveId + ", 0, '" + FromName + "','"+Title+"', '" + Content + "', GETDATE())");
            }
            catch (Exception e)
            {
                Close();
                CLog.LogError("Error SendMail: " + e.Message);
                CLog.Trace("Error SendMail: " + e.Source);
            }

            return 0;
        }

        public int DeleteMail(int MailId)
        {
            ExecuteQuery("DELETE FROM Mail WHERE Id=" + MailId);
            return 0;
        }

        //public void UpdateScore(int AccountId, string UserName, int GameId, double Gold, int ExpAdd, int WinAdd, int LostAdd, int ExitAdd)
        //{
        //    ExecuteQuery("UPDATE TableUser SET VKim=" + Gold + ", Exp=" + ExpAdd + " WHERE Id=" + AccountId);

        //    bool Ok = false;
        //    int Win = 0;
        //    int Lost = 0;
        //    int Exit = 0;
        //    int Exp = 0;
        //    int Level = 1;
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM UserGame WHERE UserId=" + AccountId + " AND GameId=" + GameId, Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            Win = Convert.ToInt32(reader["Win"]);
        //            Lost = Convert.ToInt32(reader["Lost"]);
        //            Exit = Convert.ToInt32(reader["Quit"]);
        //            Exp = Convert.ToInt32(reader["Exp"]);
        //            Ok = true;
        //        }
        //        reader.Close();
        //        Close();
        //    }
        //    catch (Exception e)
        //    {
        //        Close();
        //        CLog.LogError("Error UpdateScore: " + e.Message);
        //        CLog.Trace("Error UpdateScore: " + e.Source);
        //        return;
        //    }

        //    Win += WinAdd;
        //    Lost += LostAdd;
        //    Exit += ExitAdd;
        //    if (ExitAdd > 0)
        //    {
        //        ExpAdd = 0;
        //    }
        //    else
        //    {
        //        if (WinAdd > 0)
        //        {
        //            ExpAdd = 3;
        //        }
        //        else
        //        {
        //            ExpAdd = 1;
        //        }
        //    }
        //    Exp += ExpAdd;
        //    Level = CCommon.GetLevel(Exp);

        //    if (Ok)
        //    {
        //        ExecuteQuery("UPDATE UserGame SET Win="+Win+", Lost="+Lost+",Quit="+Exit+",Exp="+Exp+",Level="+Level+" WHERE UserId="+AccountId + " AND GameId=" + GameId);
        //    }
        //    else
        //    {
        //        ExecuteQuery("INSERT INTO UserGame(UserId, GameId, Win, Lost, Exp, Level, Quit) " + 
        //            "VALUES("+AccountId+","+GameId+","+Win+","+Lost+","+Exp+","+Level+","+Exit+")");
        //    }
        //}

        public void Open()
        {
            try
            {
                Connection = new SqlConnection(CGlobal.Config.ConnectionString);
                Connection.Open();
            }
            catch(Exception e)
            {
                CLog.LogError(e.Message);
                CLog.Trace(e.Source);
            }
        }

        public void Close()
        {
            try
            {
                if (Connection != null)
                {
                    Connection.Close();
                }
                Connection = null;
            }
            catch (Exception e)
            {
                CLog.LogError(e.Message);
                CLog.Trace(e.Source);
            }
        }

        public bool ExecuteQuery(string Query)
        {
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand(Query, Connection);
                cmd.ExecuteNonQuery();
                Close();
                return true;
            }
            catch(Exception e)
            {
                Close();
                CLog.LogError("Error ExecuteQuery: " + e.Message);
            }

            return false;
        }

        public void GetRegisterResult(int ConnId, string UserName, string Password, string Password2, ref int Result, ref int AccountId)
        {
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand("SELECT Id, UserName, Password, VKim FROM TableUser WHERE UserName='" + UserName + "'", Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    Result = 2;
                    reader.Close();
                    Close();
                    return;
                }
                reader.Close();
                Close();

                string Query = "INSERT INTO TableUser(UserName, Password, VKim, VKimLost, AvatarId, Sex, CityId, OnlineGameId, OnlineChannelId, Lock, ReceiveGift, DayCount, Active,FirstLogin, RegisterTime, MobileNumber) VALUES('" +
                    UserName + "','" +
                    CCommon.MD5(Password) + "'," +
                    CGlobal.Config.GoldInit;
                if (CGlobal.Config.EnableActiveUser)
                {
                    Query += ", 0, 0, 0, 0, -1, -1, 0, 0, 1, 0, 1, GETDATE(), '"+Password2+"')";
                }
                else
                {
                    Query += ", 0, 0, 0, 0, -1, -1, 0, 0, 1, 1, 1, GETDATE(), '" + Password2 + "')";
                }
                ExecuteQuery(Query);
                Result = 0;
            }
            catch(Exception e)
            {
                Close();
                Result = 3;
                CLog.LogError("Error GetRegisterResult: " + e.Message);
            }
        }

        //// Get Basic Info
        //public void GetUserBasicInfo(string UserName, ref int AvatarId, ref int Sex, ref int BirthYear, ref int CityId, 
        //    ref int Exp, ref int Level, ref double Gold, ref string RealName, ref string Address, ref string CMND, ref string Status)
        //{
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE UserName='" + UserName + "'", Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            AvatarId = Convert.ToInt32(reader["AvatarId"]);
        //            Sex = Convert.ToInt32(reader["Sex"]);
        //            BirthYear = Convert.ToInt32(reader["BirthYear"]);
        //            CityId = Convert.ToInt32(reader["CityId"]);
        //            Exp = Convert.ToInt32(reader["Exp"]);
        //            Level = Convert.ToInt32(reader["Level"]);
        //            Gold = CCommon.String2Double(reader["VKim"]);
        //            RealName = reader["RealName"].ToString();
        //            Address = reader["Address"].ToString();
        //            CMND = reader["CMND"].ToString();
        //            Status = reader["Status"].ToString();
        //        }
        //        reader.Close();
        //        Close();
        //    }
        //    catch (Exception e)
        //    {
        //        Close();
        //        CLog.LogError("Error GetUserBasicInfo: " + e.Message);
        //    }
        //}

        //// Update Basic Info
        //public void UpdateUserBasicInfo(int AccountId, int Sex, int BirthYear, int CityId, string RealName, string Address, string CMND, string Status)
        //{
        //    string Query = "UPDATE TableUser SET Sex=" + Sex + ", BirthYear=" + BirthYear +
        //        ", CityId=" + CityId + ", RealName='" + RealName + "', Address='" + Address +
        //        "', CMND='" + CMND + "', Status='" + Status + "' WHERE Id=" + AccountId;
        //    ExecuteQuery(Query);
        //}

        //// Get User Detail Info
        //public void GetUserDetailInfo(int AccountId, int AppId, ref int Win, ref int Lost, ref int Quit)
        //{
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM UserGame WHERE UserId=" + AccountId + " AND GameId=" + AppId, Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            Win = Convert.ToInt32(reader["Win"]);
        //            Lost = Convert.ToInt32(reader["Lost"]);
        //            Quit = Convert.ToInt32(reader["Quit"]);
        //        }
        //        reader.Close();
        //        Close();
        //    }
        //    catch (Exception e)
        //    {
        //        Close();
        //        CLog.LogError("Error GetUserDetailInfo: " + e.Message);
        //    }
        //}

        // Get User List
        public void GetUserList(int AccountId, int Type, int AppId, ref List<CUserInfo> UserList)
        {
            string Query = "";
		    switch(Type)
		    {
			    // Friend
			    case 0:
				    Query = "SELECT TOP " + CCommon.MAX_USER_LIST + " * FROM TableUser WHERE Id IN (SELECT UserId2 FROM FriendList WHERE UserId1=" + AccountId + ")";
			    break;
			    // Vip
			    case 1:
                    Query = "SELECT TOP " + CCommon.MAX_USER_LIST + " * FROM TableUser where IsOnline=1 ORDER BY VKim DESC";
			    break;
			    // Top
                case 2:
                    Query = "select top " + CCommon.MAX_USER_LIST + " *, UserGame.Level as GameLevel from TableUser inner join UserGame on TableUser.Id = UserGame.UserId where GameId=" + AppId + " order by UserGame.Exp desc;";
                break;
    			case 3:
                    Query = "select top " + CCommon.MAX_USER_LIST + " *, UserGame.Level as GameLevel from TableUser inner join UserGame on TableUser.Id = UserGame.UserId where GameId=" + AppId + " and TableUser.IsOnline=1 order by UserGame.Exp desc;";
                break;
            }

            try
            {
                Open();
                SqlCommand cmd = new SqlCommand(Query, Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CUserInfo User = new CUserInfo();
                    User.AvatarId = Convert.ToInt32(reader["AvatarId"]);
                    User.Gold = CCommon.String2Double(reader["VKim"].ToString());
                    User.UserName = reader["UserName"].ToString();
                    User.Status = reader["Status"].ToString();
                    User.UserId = Convert.ToInt32(reader["Id"]);
                    if (Type == 2 || Type == 3)
                    {
                        User.Level = Convert.ToInt32(reader["GameLevel"].ToString());
                    }
                    else
                    {
                        User.Level = 0;
                    }
                    UserList.Add(User);
                }
                reader.Close();
                Close();
            }
            catch (Exception e)
            {
                Close();
                CLog.LogError("Error GetUserList: " + e.Message);
            }
        }

        // Get Mail List
        public void GetMailList(int AccountId, ref List<CMailInfo> MailList, int Page)
        {
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand("SELECT TOP " + (CCommon.MAX_MAIL_LIST * Page) + " * FROM Mail WHERE ToUserId=" + AccountId + " ORDER BY Status, Time DESC", Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CMailInfo Mail = new CMailInfo();
                    Mail.MailDbId = Convert.ToInt32(reader["Id"]);
                    Mail.Status = Convert.ToInt32(reader["Status"]);
                    Mail.FromUserName = reader["FromUserName"].ToString();
                    Mail.Title = reader["Title"].ToString();
                    string Time = reader["Time"].ToString();
                    DateTime d = new DateTime();
                    DateTime.TryParse(Time, out d);
                    Mail.Day = d.Day;
                    Mail.Month = d.Month;
                    Mail.Year = d.Year;
                    Mail.Hour = d.Hour;
                    Mail.Min = d.Minute;
                    Mail.Sec = d.Second;
                    MailList.Add(Mail);
                }
                reader.Close();
                Close();

                for (int i = 0; i < (Page - 1) * CCommon.MAX_MAIL_LIST; i++)
                {
                    if (MailList.Count > 0)
                    {
                        MailList.RemoveAt(0);
                    }
                }
            }
            catch (Exception e)
            {
                Close();
                CLog.LogError("Error GetMailList: " + e.Message);
            }
        }

        // Get Mail Content
        public void GetMailContent(int MailDbId, ref CMailInfo Mail)
        {
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM Mail WHERE Id=" + MailDbId, Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    Mail.MailDbId = Convert.ToInt32(reader["Id"]);
                    Mail.Status = Convert.ToInt32(reader["Status"]);
                    Mail.FromUserName = reader["FromUserName"].ToString();
                    string Time = reader["Time"].ToString();
                    DateTime d = new DateTime();
                    DateTime.TryParse(Time, out d);
                    Mail.Day = d.Day;
                    Mail.Month = d.Month;
                    Mail.Year = d.Year;
                    Mail.Hour = d.Hour;
                    Mail.Min = d.Minute;
                    Mail.Sec = d.Second;
                    Mail.Title = reader["Title"].ToString();
                    Mail.Content = reader["Content"].ToString();
                }
                reader.Close();
                Close();

                // Update
                ExecuteQuery("UPDATE Mail SET Status=1 WHERE Id=" + MailDbId);
            }
            catch (Exception e)
            {
                CLog.LogError("Error GetMailContent: " + e.Message);
            }
        }

        //// Change Password, return result
        //public int ChangePassword(string UserName, string OldPass, string NewPass)
        //{
        //    int Result = 0;
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE UserName='"+UserName+"' AND Password='"+OldPass+"'", Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            Result = 0;
        //        }
        //        else
        //        {
        //            Result = 1;
        //        }
        //        reader.Close();
        //        Close();

        //        // Update
        //        if (Result == 0)
        //        {
        //            ExecuteQuery("UPDATE TableUser SET Password='"+NewPass+"' WHERE UserName='"+UserName+"'");
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        CLog.LogError("Error GetMailContent: " + e.Message);
        //        return 2;
        //    }

        //    return Result;
        //}

        //public void AddGold(string UserName, double GoldAdd)
        //{
        //    double Gold = 0;
        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM TableUser WHERE UserName='" + UserName + "'", Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            Gold = CCommon.String2Double(reader["VKim"]);
        //        }
        //        reader.Close();

        //        Gold += GoldAdd;
        //        Close();
                
        //        ExecuteQuery("UPDATE TableUser SET VKim=" + Gold + " WHERE UserName='" + UserName + "'");
        //    }
        //    catch (Exception e)
        //    {
        //        Close();
        //        CLog.LogError("Error GetUserDetailInfo: " + e.Message);
        //    }            
        //}

        public int CheckFriendListStatus(int ClientId1, int ClientId2)
        {
            int Result = -1;

            try
            {
                Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM FriendList WHERE UserId1=" + ClientId1 + " AND UserId2=" + ClientId2, Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    Result = 1;
                }
                else
                {
                    Result = 0;
                }
                reader.Close();
                Close();
            }
            catch (Exception e)
            {
                CLog.LogError("Error CheckFriendListStatus: " + e.ToString());
            }

            return Result;
        }

        //public int GetClientLevel(int ClientId, int AppId)
        //{
        //    int Level = 1;

        //    try
        //    {
        //        Open();
        //        SqlCommand cmd = new SqlCommand("SELECT * FROM UserGame WHERE UserId=" + ClientId + " AND GameId=" + AppId, Connection);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            Level = Convert.ToInt32(reader["Level"]); ;
        //        }
        //        reader.Close();
        //        Close();
        //    }
        //    catch (Exception e)
        //    {
        //        CLog.LogError("Error CheckFriendListStatus: " + e.ToString());
        //    }

        //    return Level;
        //}
    }
}
